package com.xxl.job.admin.controller

import com.xxl.job.admin.core.model.KettleProcedure
import com.xxl.job.core.biz.model.ReturnT
import groovy.json.JsonOutput
import org.springframework.stereotype.Controller
import org.springframework.ui.Model
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.ResponseBody
import org.springframework.web.context.ContextLoader
import sun.org.mozilla.javascript.internal.json.JsonParser


/**
 * Created by Administrator on 2018-09-03.
 */
@Controller
@RequestMapping("/procedure")
public class ProcedureController {

    def ora_date_format = "yyyy-mm-dd hh24:mi:ss"
    def booleanFlag = ["Y","N"]
    def gradeList = [[VALUE:1,LABLE:"一级"],[VALUE:2,LABLE:"二级"],[VALUE:3,LABLE:"三级"]]
    def dbCfg = ContextLoader.getCurrentWebApplicationContext().getBean("dataSource");
    def db ={
        return groovy.sql.Sql.newInstance(dbCfg.jdbcUrl, dbCfg.user, dbCfg.password, dbCfg.driverClass)
    }

    def count = { sql ->
        return db().firstRow("select count(1) COUNTN from (${sql}) ".toString()).COUNTN
    }

    def max = {table,maxColumn ->
        return db().firstRow("select max(${maxColumn}) MAXN from (${table}) ".toString()).MAXN?:0
    }

    def getMaxId = {OBJECT_ID,OBJECT_NAME ->
        def one =  db().firstRow("""select * from KETTLE_PROCEDURE t
                 where 1=1
                ${OBJECT_ID!=null? "and t.OBJECT_ID = ${OBJECT_ID}": "and upper(t.OBJECT_NAME) like '%${String.valueOf(OBJECT_NAME).trim().toUpperCase()}%'"}
                """.toString())
        if(one){
            //println one.get("OBJECT_ID")
            return  one.OBJECT_ID
        }
        return max("KETTLE_PROCEDURE","OBJECT_ID")+1
    }



    @RequestMapping
    public String index(Model model,
                        @RequestParam(required = false, defaultValue = "") String OBJECT_NAME,
                        @RequestParam(required = false, defaultValue = "") String PROCEDURE_NAME,
                        @RequestParam(required = false, defaultValue = "") String IS_HISTORY,
                        @RequestParam(required = false, defaultValue = "0") int start,
                        @RequestParam(required = false, defaultValue = "10") int length){

        def procedureList = db().rows(" SELECT DISTINCT t.PROCEDURE_NAME VALUE, t.PROCEDURE_NAME LABLE FROM KETTLE_PROCEDURE  t WHERE T.PROCEDURE_NAME IS NOT NULL ORDER BY 2")
        model.addAttribute("procedureList",procedureList)
        model.addAttribute("booleanFlag",booleanFlag)
        return "procedure/procedure.index"
    }

    @RequestMapping("/pageList")
    @ResponseBody
    public Map<String, Object> pageList(
            @RequestParam(required = false, defaultValue = "") String OBJECT_NAME,
            @RequestParam(required = false, defaultValue = "") String PROCEDURE_NAME,
            @RequestParam(required = false, defaultValue = "") String IS_HISTORY,
            @RequestParam(required = false, defaultValue = "0") int start,
            @RequestParam(required = false, defaultValue = "10") int length) {

        def sql = """
            select
                OBJECT_ID             ,
                OBJECT_NAME           ,
                to_char(START_TIME,'${ora_date_format}' )    START_TIME       ,
                INTERVAL              ,
                to_char(RUNTIME,'${ora_date_format}' )  RUNTIME                ,
                OBJECT_DESC           ,
                ROWCOUNT              ,
                MAX_INTERVAL          ,
                ISRERUN               ,
                CURRENT_COUNT         ,
                MAX_COUNT             ,
                IS_HISTORY            ,
                to_char(HIS_START_TIME,'${ora_date_format}')     HIS_START_TIME  ,
                to_char(HIS_END_TIME,'${ora_date_format}'  )   HIS_END_TIME           ,
                to_char(HIS_RUN_TIME,'${ora_date_format}'  )   HIS_RUN_TIME           ,
                MAX_PROCESS_DATE      ,
                WAIT_INTERVAL         ,
                TIME_CONSUMING        ,
                ACTIVE                ,
                RUN_FLAG              ,
                HIS_RUN_FLAG          ,
                PROCEDURE_NAME        ,
                LEVEL_SEQ
        from KETTLE_PROCEDURE t
        where 1=1
            ${String.valueOf(IS_HISTORY)?"  AND T.IS_HISTORY = '${IS_HISTORY}'":""}
            ${String.valueOf(OBJECT_NAME)?" AND INSTR(upper(T.OBJECT_NAME),'${OBJECT_NAME.toUpperCase()}') !=0":""}
            ${String.valueOf(PROCEDURE_NAME)?" AND INSTR(upper(T.PROCEDURE_NAME),'${PROCEDURE_NAME.toUpperCase()}') !=0":"" }
        """
        //println sql
        def list = db().rows(sql.toString(),start,length)
        def total = count(sql)
        def map = [recordsFiltered:total,recordsTotal:total,data:list]
        return map
    }

    /**
     * 根据OBJECT_ID获取单条记录
     * */
    @RequestMapping("/getIndex")
    @ResponseBody
    public KettleProcedure getIndex(@RequestParam(required = false, defaultValue = "") String OBJECT_ID){
        def map = KettleProcedure.newInstance()
        if(OBJECT_ID){
            map = db().firstRow("select * from KETTLE_PROCEDURE t where t.OBJECT_ID = ${OBJECT_ID}".toString())
        }
        return map
    }


    /**
     * 根据OBJECT_ID更新KETTLE_PROCEDURE
     * */
    @RequestMapping("/updateOne")
    @ResponseBody
    public ReturnT updateOne(KettleProcedure kettleProcedure ){
        Map mapData = kettleProcedure.properties
        //println mapData
        ReturnT returnT = new ReturnT()

        def sql = """
        MERGE INTO KETTLE_PROCEDURE TA
        USING (SELECT :OBJECT_ID OBJECT_ID,
                      upper(:OBJECT_NAME) OBJECT_NAME,
                      TO_DATE(:START_TIME, '${ora_date_format}') START_TIME,
                      :INTERVAL INTERVAL,
                      TO_DATE(:RUNTIME, '${ora_date_format}') RUNTIME,
                      :OBJECT_DESC OBJECT_DESC,
                      :ROWCOUNT ROWCOUNT,
                      :MAX_INTERVAL MAX_INTERVAL,
                      :ISRERUN ISRERUN,
                      :CURRENT_COUNT CURRENT_COUNT,
                      :MAX_COUNT MAX_COUNT,
                      :IS_HISTORY IS_HISTORY,
                      TO_DATE(:HIS_START_TIME, '${ora_date_format}') as HIS_START_TIME,
                      TO_DATE(:HIS_END_TIME, '${ora_date_format}') as HIS_END_TIME,
                      TO_DATE(:HIS_RUN_TIME, '${ora_date_format}') as HIS_RUN_TIME,
                      :MAX_PROCESS_DATE as MAX_PROCESS_DATE,
                      :WAIT_INTERVAL WAIT_INTERVAL,
                      :TIME_CONSUMING TIME_CONSUMING,
                      :ACTIVE ACTIVE,
                      :RUN_FLAG as RUN_FLAG,
                      :HIS_RUN_FLAG as HIS_RUN_FLAG,
                      upper(:PROCEDURE_NAME) as PROCEDURE_NAME,
                      :LEVEL_SEQ as LEVEL_SEQ
                 FROM DUAL) TB
        ON (TA.OBJECT_ID = TB.OBJECT_ID)
        WHEN MATCHED THEN
          UPDATE
             SET TA.OBJECT_NAME      = TB.OBJECT_NAME,
                 TA.OBJECT_DESC      = TB.OBJECT_DESC,
                 TA.PROCEDURE_NAME   = TB.PROCEDURE_NAME,
                 TA.START_TIME       = TB.START_TIME,
                 TA.INTERVAL         = TB.INTERVAL,
                 TA.MAX_INTERVAL     = TB.MAX_INTERVAL,
                 TA.IS_HISTORY       = TB.IS_HISTORY,
                 TA.HIS_START_TIME   = TB.HIS_START_TIME,
                 TA.HIS_END_TIME     = TB.HIS_END_TIME,
                 TA.HIS_RUN_TIME     = TB.HIS_RUN_TIME,
                 TA.MAX_PROCESS_DATE = TB.MAX_PROCESS_DATE,
                 TA.WAIT_INTERVAL    = TB.WAIT_INTERVAL,
                 TA.LEVEL_SEQ        = TB.LEVEL_SEQ，
                 TA.ACTIVE           = TB.ACTIVE
        WHEN NOT MATCHED THEN
          INSERT
            (TA.OBJECT_ID,
             TA.OBJECT_NAME,
             TA.OBJECT_DESC,
             TA.PROCEDURE_NAME,
             TA.START_TIME,
             TA.INTERVAL,
             TA.MAX_INTERVAL,
             TA.IS_HISTORY,
             TA.HIS_START_TIME,
             TA.HIS_END_TIME,
             TA.MAX_PROCESS_DATE,
             TA.WAIT_INTERVAL,
             TA.LEVEL_SEQ,
             TA.RUNTIME,
             TA.ROWCOUNT,
             TA.ISRERUN,
             TA.CURRENT_COUNT,
             TA.MAX_COUNT,
             TA.HIS_RUN_TIME,
             TA.TIME_CONSUMING,
             TA.HIS_RUN_FLAG,
             TA.ACTIVE,
             TA.RUN_FLAG)
          VALUES
            (TB.OBJECT_ID,
             TB.OBJECT_NAME,
             TB.OBJECT_DESC,
             TB.PROCEDURE_NAME,
             TB.START_TIME,
             TB.INTERVAL,
             TB.MAX_INTERVAL,
             TB.IS_HISTORY,
             TB.HIS_START_TIME,
             TB.HIS_END_TIME,
             TB.MAX_PROCESS_DATE,
             TB.WAIT_INTERVAL,
             TB.LEVEL_SEQ,
             TB.RUNTIME,
             TB.ROWCOUNT,
             TB.ISRERUN,
             TB.CURRENT_COUNT,
             TB.MAX_COUNT,
             TB.HIS_RUN_TIME,
             TB.TIME_CONSUMING,
             TB.HIS_RUN_FLAG,
             TB.ACTIVE,
             TB.RUN_FLAG)
        """ ;

        try{
            def OBJECT_ID = getMaxId(mapData.OBJECT_ID?:null,mapData.OBJECT_NAME)

            mapData.put("OBJECT_ID",OBJECT_ID)

            db().withTransaction {
                def a = db().execute(mapData,sql.toString())
            }
            returnT.setCode(ReturnT.SUCCESS_CODE)
        }catch (Exception e){
            returnT.setCode(ReturnT.FAIL_CODE)
            returnT.setMsg("操作失败:${e.getMessage()}")
        }finally{
            return returnT
        }
    }

    /**
     * 根据OBJECT_ID暂停KETTLE_PROCEDURE
     * */
    @RequestMapping("/changeActive")
    @ResponseBody
    public  ReturnT<String> changeActive(@RequestParam(required = false, defaultValue = "-1") int OBJECT_ID,
                                         @RequestParam(required = false, defaultValue = "") String ACTIVE){

        ReturnT<String> returnT = new ReturnT<>()
        def sql = """
            UPDATE KETTLE_PROCEDURE
            SET ACTIVE = :ACTIVE
            WHERE  OBJECT_ID = :OBJECT_ID
        """ ;

        try{

            db().withTransaction {
                def a = db().executeUpdate([ACTIVE:ACTIVE,OBJECT_ID:OBJECT_ID],sql.toString())
            }
            returnT.setCode(ReturnT.SUCCESS_CODE)
        }catch (Exception e){
            returnT.setCode(ReturnT.FAIL_CODE)
            returnT.setMsg("操作失败:${e.getMessage()}")
        }finally{
            return returnT
        }
    }

    /**
     * 根据OBJECT_ID删除KETTLE_PROCEDURE
     * */
    @RequestMapping("/delete")
    @ResponseBody
    public  ReturnT<String> delete(@RequestParam(required = false, defaultValue = "-1") int OBJECT_ID){
        ReturnT<String> returnT = new ReturnT<>()
        def sql = """
            DELETE FROM KETTLE_PROCEDURE WHERE  OBJECT_ID = :OBJECT_ID
        """
        try{
            db().withTransaction {
                def a = db().execute([OBJECT_ID:OBJECT_ID],sql.toString())
            }
            returnT.setCode(ReturnT.SUCCESS_CODE)
        }catch (Exception e){
            returnT.setCode(ReturnT.FAIL_CODE)
            returnT.setMsg("操作失败:${e.getMessage()}")
        }finally{
            return returnT
        }
    }


    @RequestMapping("/indexLog")
    public String indexLog(Model model,
                           @RequestParam(required = false, defaultValue = "") PROCEDURE_NAME ,
                           @RequestParam(required = false, defaultValue = "0") int start,
                           @RequestParam(required = false, defaultValue = "10") int length){

        def procedureList = db().rows(" SELECT DISTINCT t.PROCEDURE_NAME VALUE, t.PROCEDURE_NAME LABLE FROM KETTLE_PROCEDURE  t WHERE T.PROCEDURE_NAME IS NOT NULL ORDER BY 2")
        model.addAttribute("procedureList",procedureList)
        model.addAttribute("PROCEDURE_NAME",PROCEDURE_NAME)
        //println PROCEDURE_NAME
        return "procedure/procedure.log"
    }

    /**
     * 过程日志 ETL_PROCEDURE_HIS
     * @param OBJECT_ID
     * @param page
     * @param limit
     * @return
     */
    @RequestMapping("/etlHisPageList")
    @ResponseBody
    public Map<String, Object> etlHisPageList(
            @RequestParam(required = false, defaultValue = "") PROCEDURE_NAME ,
            @RequestParam(required = false, defaultValue = "") START_TIME ,
            @RequestParam(required = false, defaultValue = "") END_TIME ,
            @RequestParam(required = false, defaultValue = "0") int start,
            @RequestParam(required = false, defaultValue = "10") int length) {

        def sql = """
            select * from ETL_PROCEDURE_HIS t where 1=1
            ${String.valueOf(PROCEDURE_NAME).trim().length()>0  ?" AND t.PROCEDURE_NAME  like '%${PROCEDURE_NAME}%' ":"" }
            ${String.valueOf(START_TIME).trim().length()>0?"  AND T.START_TIME >=TO_DATE('${START_TIME}','${ora_date_format}' ) ":""}
            ${String.valueOf(END_TIME).trim().length()>0?"  AND T.END_TIME <=TO_DATE('${END_TIME}','${ora_date_format}' ) ":""}
        """
        def list = db().rows(sql.toString(),start,length)
        def total = count(sql)
        def map = [recordsFiltered:total,recordsTotal:total,data:list]
        return map
    }


    /**
     * 根据OBJECT_ID删除KETTLE_PROCEDURE_his
     * */
    @RequestMapping("/deleteEtlHis")
    @ResponseBody
    public  ReturnT<String> deleteEtlHis(
            @RequestParam(required = false, defaultValue = "") PROCEDURE_NAME ,
            @RequestParam(required = false, defaultValue = "") START_TIME ,
            @RequestParam(required = false, defaultValue = "") END_TIME   ){
        ReturnT<String> returnT = new ReturnT<>()

        def sql = """
            DELETE from ETL_PROCEDURE_HIS t where 1=1
            ${  String.valueOf(PROCEDURE_NAME).trim().length()>0  ?" AND t.PROCEDURE_NAME  like '%${PROCEDURE_NAME}%' ":"" }
            ${String.valueOf(START_TIME).trim().length()>0?"  AND T.START_TIME >=TO_DATE('${START_TIME}','${ora_date_format}' ) ":""}
            ${String.valueOf(END_TIME).trim().length()>0?"  AND T.END_TIME <=TO_DATE('${END_TIME}','${ora_date_format}' ) ":""}
        """

        //println sql
        try{
            db().withTransaction {
                def a = db().execute(sql.toString())
            }
            returnT.setCode(ReturnT.SUCCESS_CODE)
        }catch (Exception e){
            returnT.setCode(ReturnT.FAIL_CODE)
            returnT.setMsg("操作失败:${e.getMessage()}")
        }finally{
            return returnT
        }
    }

    /**
     * 重跑清单
     * @param TABLE_NAME
     * @return
     */
    @RequestMapping("/etlReRunList")
    @ResponseBody
    public Map<String, Object> etlReRunList(
            @RequestParam(required = false, defaultValue = "") String TABLE_NAME) {

        def sql = """
                SELECT T1.TABLE_NAME,
                       T1.PROCEDURE_NAME,
                       T1.LEVEL_SEQ,
                       TT.START_TIME,
                       TT.END_TIME,
                       TT.INSERT_TIME
                  FROM PROCEDURE_RELATED T1
                  LEFT JOIN (SELECT T2.*,
                                    ROW_NUMBER() OVER(PARTITION BY T2.PROCEDURE_NAME ORDER BY T2.START_TIME DESC) RAN
                               FROM RUN_HIS_SET T2
                              WHERE 1 = 1) TT
                    ON UPPER(T1.PROCEDURE_NAME) = UPPER(TT.PROCEDURE_NAME)
                   AND RAN = 1
                 WHERE 1=1
                  ${ String.valueOf(TABLE_NAME).length() >0 ?" AND T1.TABLE_NAME in (${TABLE_NAME}) ":"" }

        """
        //println sql
        def list = db().rows(sql.toString())
        def map = [data:list]
        return map
    }

    /**
     * 重跑清单
     * @param
     * @return TABLE_NAME
     */
    @RequestMapping("/etlReRunTableList")
    @ResponseBody
    public   Map<String, Object>  etlReRunTableList() {
        ReturnT<String> returnT = new ReturnT<>()
        def sql = """
        SELECT DISTINCT T.TABLE_NAME FROM PROCEDURE_RELATED T
        """
        //println sql
        def list = db().rows(sql.toString())
        def map = [data:list]
        return map
    }



    /**
     * 过程重跑调用
     * @param PROCEDURE
     * @param START_TIME
     * @param END_TIME
     * @param THREAD_COUNT
     * @return
     */
    @RequestMapping("/etlReRunExecute")
    @ResponseBody
    public  ReturnT<String> etlReRunExecute(@RequestParam(required = false, defaultValue = "Y") String TABLE_NAME,
                                            @RequestParam(required = false, defaultValue = "") String reRunTime,
                                            @RequestParam(required = false, defaultValue = "1") int THREAD_COUNT){
        ReturnT<String> returnT = new ReturnT<>()
        def dateArray = reRunTime.split(" - ");
        def START_TIME = dateArray[0]
        def END_TIME = dateArray[1]
        def PROCEDURE = "Y"
        def sql
        if(TABLE_NAME!= "Y"){
            sql = """
            SELECT WM_CONCAT(T.PROCEDURE_NAME) PROCEDURE_NAME
            FROM PROCEDURE_RELATED T
            WHERE 1 = 1
            AND T.TABLE_NAME IN (${TABLE_NAME})
            ORDER BY T.LEVEL_SEQ
            """
            PROCEDURE = db().firstRow(sql.toString()).PROCEDURE_NAME
        }

        sql = """
           CALL P_RUN_HIS_SET(:PROCEDURE,:START_TIME,:END_TIME,:THREAD_COUNT)
        """

        //println "[PROCEDURE:${PROCEDURE},START_TIME:${START_TIME},END_TIME:${END_TIME},THREAD_COUNT:${THREAD_COUNT}"
        try{
            Thread.start {
                def a = db().execute([PROCEDURE:PROCEDURE,START_TIME:START_TIME,END_TIME:END_TIME,THREAD_COUNT:THREAD_COUNT],sql.toString())
            }
            returnT.setCode(ReturnT.SUCCESS_CODE)
            returnT.msg = "过程已调起，稍后请查看执行状态"
        }catch (Exception e){
            returnT.setCode(ReturnT.FAIL_CODE)
            returnT.setMsg("操作失败:${e.getMessage()}")
        }finally{
            println returnT
            return returnT
        }
    }

}
